﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.Mvc;
using EvidenceLib.System;
using EvidenceService.Core;
using EvidenceService.System;
using EvidenceWeb.Models;
using EvidenceWeb.Util;
using NHibernate;
using MSExcel = Microsoft.Office.Interop.Excel;

namespace EvidenceWeb.Controllers
{
    public class PrintBillController : Controller
    {
        //
        // GET: /PrintBill/

        public ActionResult EvidenceBillPage()
        {
            return View();
        }

        public ActionResult StockBillPage()
        {
            return View();
        }

        #region 物品单据打印

        public static string evidenceBillpath = ConfigurationManager.AppSettings["EvidenceBillPath"];
        public static string stockBillpath = ConfigurationManager.AppSettings["StockBillPath"];

        public FileResult PrintEvidenceBillList()
        {
            using (ISession session = HbmSessionFactory.GetSession())
            {
                IList<EvidenceBillPrint> eviBillList = new List<EvidenceBillPrint>();
                string queryParam = Request["queryParams"];
                EvidenceBillQuery query = null;
                string isAllPermission = "TURE";
                if (queryParam != null)
                    query = JsonSerializerUtil.JsonToEntity<EvidenceBillQuery>(queryParam);
                else
                    query = new EvidenceBillQuery();

                User user = HttpContext.Session["User"] as User;

                RoleService roleSvr = new RoleService(session);

                if (!roleSvr.IsPermission(user.Id, "queryer"))
                {
                    isAllPermission = "FALSE";
                }

                try
                {
                    IDbCommand cmd = session.Connection.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Ev_ExportEvidenceBillToExcel";

                    SqlParameter pPermission = new SqlParameter("@IsAllPermission", SqlDbType.NVarChar);
                    SqlParameter pUserDept = new SqlParameter("@pUserDept", SqlDbType.NVarChar);
                    SqlParameter pStates = new SqlParameter("@StockStates", SqlDbType.NVarChar);
                    SqlParameter pSerialNo = new SqlParameter("@SerialNo", SqlDbType.NVarChar);
                    SqlParameter pDeptId = new SqlParameter("@TransferDeptId", SqlDbType.NVarChar);
                    SqlParameter pStartTime = new SqlParameter("@BillStartTime", SqlDbType.NVarChar);
                    SqlParameter pEndTime = new SqlParameter("@BillEndTime", SqlDbType.NVarChar);
                    SqlParameter pPoliceName = new SqlParameter("@PoliceName", SqlDbType.NVarChar);
                    SqlParameter pEviName = new SqlParameter("@EvidenceName", SqlDbType.NVarChar);

                    pPermission.Value = isAllPermission;
                    pUserDept.Value = user.Department == null ? 0 : user.Department.Id;
                    pStates.Value = query.StockState;
                    pSerialNo.Value = query.SerialNo;
                    pDeptId.Value = query.DeptId;
                    pStartTime.Value = query.BeginTime == null ? "" : query.BeginTime.Value.ToString();
                    pEndTime.Value = query.EndTime == null ? "" : query.EndTime.Value.AddDays(1).ToString(); ;
                    pPoliceName.Value = query.TransferName;
                    pEviName.Value = query.EvidenceName;

                    cmd.Parameters.Add(pPermission);
                    cmd.Parameters.Add(pUserDept);
                    cmd.Parameters.Add(pStates);
                    cmd.Parameters.Add(pSerialNo);
                    cmd.Parameters.Add(pDeptId);
                    cmd.Parameters.Add(pStartTime);
                    cmd.Parameters.Add(pEndTime);
                    cmd.Parameters.Add(pPoliceName);
                    cmd.Parameters.Add(pEviName);

                    IDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        EvidenceBillPrint eviBill = new EvidenceBillPrint();
                        eviBill.CaseName = reader.IsDBNull(0) ? "" : reader.GetString(0);
                        eviBill.EvidenceName = reader.IsDBNull(1) ? "" : reader.GetString(1);
                        eviBill.CaseName = reader.IsDBNull(2) ? "" : reader.GetString(2);
                        eviBill.TransferName = reader.IsDBNull(3) ? "" : reader.GetString(3);
                        eviBill.EviOwnerName = reader.IsDBNull(4) ? "" : reader.GetString(4);
                        eviBill.Measures = reader.IsDBNull(5) ? "" : reader.GetString(5);
                        eviBill.MeasureTime = reader.IsDBNull(6) ? "" : reader.GetString(6);
                        eviBill.StorageName = reader.IsDBNull(7) ? "" : reader.GetString(7);
                        eviBill.StroageTime = reader.IsDBNull(8) ? "" : reader.GetString(8);
                        eviBill.Operating = reader.IsDBNull(9) ? "" : reader.GetString(9);
                        eviBill.OperateTime = reader.IsDBNull(10) ? "" : reader.GetString(10);
                        eviBill.Description = reader.IsDBNull(11) ? "" : reader.GetString(11);
                        eviBill.CaseType = reader.IsDBNull(12) ? "" : reader.GetString(12);
                        eviBillList.Add(eviBill);
                    }
                    string filePath = CreateExcelDocumentByList(eviBillList);
                    return File(filePath, "application/vnd.ms-excel", "EvidenceBill.xlsx");
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        private string CreateExcelDocumentByList(IList<EvidenceBillPrint> eviBillList)
        {
            User user = HttpContext.Session["User"] as User;
            Object missing = Missing.Value;
            MSExcel.Application excelApp = new MSExcel.ApplicationClass(); ;  //Excel应用程序 
            string path = Server.MapPath(evidenceBillpath);
            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }

            if (System.IO.File.Exists(path + "EvidenceBill.xlsx"))
            {
                System.IO.File.Delete(path + "EvidenceBill.xlsx");
            }
            MSExcel._Workbook xBk = excelApp.Workbooks.Add(true);  //Excel文档 
            MSExcel._Worksheet xSt;

            xSt = (MSExcel._Worksheet)xBk.ActiveSheet;
            xSt.Name = "涉案财务登记表";
            var RowAll = excelApp.get_Range(excelApp.Cells[1, 1], excelApp.Cells[(6 + eviBillList.Count), 14]);
            RowAll.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            excelApp.Cells[1, 1] = "案件涉案财物登记表";
            MSExcel.Range r1 = excelApp.get_Range(excelApp.Cells[1, 1], excelApp.Cells[1, 14]);
            r1.MergeCells = true;
            r1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            r1.Font.Size = 14;
            excelApp.Cells[2, 1] = "单位：" + user.Department == null ? "" : user.Department.Name;
            MSExcel.Range r2 = excelApp.get_Range(excelApp.Cells[2, 1], excelApp.Cells[2, 9]);
            r2.MergeCells = true;
            excelApp.Cells[2, 10] = "编号：";

            MSExcel.Range r2_1 = excelApp.get_Range(excelApp.Cells[2, 10], excelApp.Cells[2, 13]);
            r2_1.MergeCells = true;
            r2_1.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignLeft;
            excelApp.Cells[3, 1] = "序号";
            MSExcel.Range r3_1 = excelApp.get_Range(excelApp.Cells[3, 1], excelApp.Cells[4, 1]);
            r3_1.MergeCells = true;
            r3_1.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
            excelApp.Cells[3, 2] = "案件编号";
            MSExcel.Range r3_2 = excelApp.get_Range(excelApp.Cells[3, 2], excelApp.Cells[4, 2]);
            r3_2.MergeCells = true;
            excelApp.Cells[3, 3] = "案件类型";
            MSExcel.Range r3_3 = excelApp.get_Range(excelApp.Cells[3, 3], excelApp.Cells[4, 3]);
            r3_3.MergeCells = true;
            r3_3.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
            //r3_3.ColumnWidth = 80;
            excelApp.Cells[3, 4] = "财物名称及特征";
            MSExcel.Range r3_4 = excelApp.get_Range(excelApp.Cells[3, 4], excelApp.Cells[4, 4]);
            r3_4.MergeCells = true;
            r3_4.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
            excelApp.Cells[3, 5] = "案件名称";
            MSExcel.Range r3_5 = excelApp.get_Range(excelApp.Cells[3, 5], excelApp.Cells[4, 5]);
            r3_5.MergeCells = true;
            r3_5.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
            excelApp.Cells[3, 6] = "移送人（签字）";
            MSExcel.Range r3_6 = excelApp.get_Range(excelApp.Cells[3, 6], excelApp.Cells[4, 6]);
            r3_6.MergeCells = true;
            r3_6.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
            excelApp.Cells[3, 7] = "财物持有人";
            MSExcel.Range r3_7 = excelApp.get_Range(excelApp.Cells[3, 7], excelApp.Cells[4, 7]);
            r3_7.MergeCells = true;
            r3_7.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;


            excelApp.Cells[3, 8] = "采取措施情况";
            MSExcel.Range r3_8 = excelApp.get_Range(excelApp.Cells[3, 8], excelApp.Cells[3, 9]);
            r3_8.MergeCells = true;
            r3_8.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
            excelApp.Cells[3, 10] = "保管状态";
            MSExcel.Range r3_10 = excelApp.get_Range(excelApp.Cells[3, 10], excelApp.Cells[3, 11]);
            r3_10.MergeCells = true;
            r3_10.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
            excelApp.Cells[3, 12] = "处理情况";
            MSExcel.Range r3_12 = excelApp.get_Range(excelApp.Cells[3, 12], excelApp.Cells[3, 13]);
            r3_12.MergeCells = true;
            r3_12.HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;
            excelApp.Cells[4, 8] = "措施种类";
            excelApp.Cells[4, 9] = "采取时间";
            excelApp.Cells[4, 10] = "存放位置";
            excelApp.Cells[4, 11] = "入库时间";
            excelApp.Cells[4, 12] = "处理方式";
            excelApp.Cells[4, 13] = "处理时间";
            excelApp.Cells[4, 14] = "备注";
            for (int i = 0; i < eviBillList.Count; i++)
            {
                excelApp.Cells[(5 + i), 1] = (i + 1).ToString();
                excelApp.Cells[(5 + i), 2] = eviBillList[i].CaseNumber;
                excelApp.Cells[(5 + i), 3] = eviBillList[i].CaseType;
                excelApp.Cells[(5 + i), 4] = eviBillList[i].EvidenceName;
                excelApp.Cells[(5 + i), 5] = eviBillList[i].CaseName;
                excelApp.Cells[(5 + i), 6] = eviBillList[i].TransferName;
                excelApp.Cells[(5 + i), 7] = eviBillList[i].EviOwnerName;
                excelApp.Cells[(5 + i), 8] = eviBillList[i].Measures;
                excelApp.Cells[(5 + i), 9] = eviBillList[i].MeasureTime;
                excelApp.Cells[(5 + i), 10] = eviBillList[i].StorageName;
                excelApp.Cells[(5 + i), 11] = eviBillList[i].StroageTime;
                excelApp.Cells[(5 + i), 12] = eviBillList[i].Operating;
                excelApp.Cells[(5 + i), 13] = eviBillList[i].OperateTime;
                excelApp.Cells[(5 + i), 14] = eviBillList[i].Description;
            }
            excelApp.Cells[(5 + eviBillList.Count), 2] = "保管人：";
            excelApp.Cells[(6 + eviBillList.Count), 1] = "填表要求：\r\n     1、办理刑事案件过程中采取扣留、调取、先行登记保存、抽样取证、追缴、收缴、没收等方式提取或者固定的涉案财物，以及在办案过程中依法收取的保证金均应如实登记； \r\n     2、存放位置填写物品具体存放仓位编号； \r\n     3、对涉案财物采取的措施有变更的，应在“备注”栏注明变更情况及变更时间； \r\n     4、对财物的“处理方式”分为返还、变卖、拍卖、销毁、上缴国库、厂家回收等。";
            MSExcel.Range r = excelApp.get_Range(excelApp.Cells[(6 + eviBillList.Count), 1], excelApp.Cells[(6 + eviBillList.Count), 14]);
            r.MergeCells = true;
            r.RowHeight = 100;
            xSt.get_Range(excelApp.Cells[5, 9], excelApp.Cells[(5 + eviBillList.Count), 9]).NumberFormatLocal = "YYYY-MM-DD HH:MM:SS";

            xSt.get_Range(excelApp.Cells[5, 11], excelApp.Cells[(5 + eviBillList.Count), 11]).NumberFormatLocal = "YYYY-MM-DD HH:MM:SS";

            excelApp.Visible = false;
            xBk.SaveAs(path + "EvidenceBill.xlsx", missing, missing, missing, missing, missing, MSExcel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing);
            xBk.Close(missing, missing, missing);
            excelApp.Quit();

            //xBk = null;
            //excelApp = null;
            //Object format = MSExcel.XlFileFormat.xlWorkbookDefault;
            //excelDoc.SaveAs(path, missing, missing, missing, missing, missing, MSExcel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);

            return path + "EvidenceBill.xlsx";
        }

        #endregion
    }
}
